NULL Functions

SQL NULL Functions

Sure, let's walk through examples of using SQL NULL functions:

1. IS NULL Operator:

Suppose you have a table named employees with columns employee_id, first_name, and last_name. You want to retrieve the rows where the last_name is not specified (i.e., NULL):




SELECT * FROM employees WHERE last_name IS NULL;

This query will return all rows from the employees table where the last_name column is NULL.



2. IS NOT NULL Operator:

Now, let's retrieve the rows where the last_name is specified (i.e., not NULL):




SELECT * FROM employees WHERE last_name IS NOT NULL;

This query will return all rows from the employees table where the last_name column is not NULL.



3. COALESCE Function:

Suppose you have a table named products with columns product_id, product_name, and unit_price. Some products have a NULL value for unit_price. You want to display 'N/A' for those products:




SELECT product_id, product_name, COALESCE(unit_price, 'N/A') AS unit_price FROM products;

This query will return all rows from the products table, replacing NULL values in the unit_price column with 'N/A'.



4. NULLIF Function:

Suppose you have a table named students with columns student_id, name, and grade. You want to set the grade to NULL if it's equal to 'F':




SELECT student_id, name, NULLIF(grade, 'F') AS grade FROM students;




This query will return all rows from the students table, replacing 'F' grades with NULL.

These examples demonstrate how SQL NULL functions and operators can be used to handle NULL values effectively in SQL queries, making data retrieval and manipulation more robust and accurate.